package com.jy.api.dao;

import com.jy.api.domain.*;
import com.jy.api.signUpReq.stock.RecordDetailsSignUpReq;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import java.util.Date;
import java.util.List;
import java.util.Map;

/**
 * Created by lihu on 2018/2/1.
 */
public interface StockTransactionDao extends JpaRepository<StockChangeTransaction, String>, JpaSpecificationExecutor<StockChangeTransaction> {

    /**
     * 获取单次盘点所有记录
     *
     * @param refId
     * @return
     */
    List<StockChangeTransaction> findByRefIdOrderByIdDesc(String refId);


    /***
     * 条件查询商品出入库信息
     *
     * @param signUpReq
     * @return
     */
    List<Map<String, Object>> queryStockDetails(RecordDetailsSignUpReq signUpReq);


    /***
     * 条件查询商品出入库总数量
     *
     * @param signUpReq
     * @return
     */
    long queryStockDetailsNumber(RecordDetailsSignUpReq signUpReq);

    List<StockChangeTransaction> findListByRefIdAndStoreId(String refId, String storeId);

    Page<StockChangeTransaction> findListBySkuIdAndTypeAndStockLevel(String skuId, StockType type, StockLevel level, Pageable pageable);

    Page<StockChangeTransaction> findByStoreIdAndAddTimeBetweenAndTypeAndSource(String storeId, Date begin, Date end, StockType type, StockChangeReason reason, Pageable pageable);

    Page<StockChangeTransaction> findAllByStoreIdAndAddTimeGreaterThanEqualAndStockLevelOrderByIdDesc(String storeId, Date time, StockLevel level, Pageable pageable);

    Page<StockChangeTransaction> findAllByStoreIdAndTypeAndAddTimeGreaterThanEqualAndStockLevelAndSourceOrderByIdDesc(String storeId, StockType type, Date time, StockLevel level,StockChangeReason reason, Pageable pageable);

    Page<StockChangeTransaction> findAllByStoreIdAndAddTimeGreaterThanEqualAndOperatorIdAndStockLevelOrderByIdDesc(String storeId, Date time, String operatorId, StockLevel level, Pageable pageable);

    Page<StockChangeTransaction> findAllByStoreIdAndAddTimeGreaterThanEqualAndSkuIdAndStockLevelOrderByIdDesc(String storeId, Date time, String skuId, StockLevel level, Pageable pageable);

    Page<StockChangeTransaction> findAllByStoreIdAndTypeAndSkuIdAndAddTimeGreaterThanEqualAndStockLevelAndSourceOrderByIdDesc(String storeId, StockType type, String skuId, Date time, StockLevel level,StockChangeReason reason, Pageable pageable);

    Page<StockChangeTransaction> findAllByStoreIdAndTypeAndOperatorIdAndAddTimeGreaterThanEqualAndStockLevelAndSourceOrderByIdDesc(String storeId, StockType type, String opId, Date time, StockLevel level,StockChangeReason reason, Pageable pageable);

    Page<StockChangeTransaction> findAllByStoreIdAndTypeAndOperatorIdAndSkuIdAndAddTimeGreaterThanEqualAndStockLevelAndSourceOrderByIdDesc(String storeId, StockType type, String opId, String skuId, Date time, StockLevel level,StockChangeReason reason, Pageable pageable);

    Page<StockChangeTransaction> findAllByStoreIdAndAddTimeGreaterThanEqualAndSkuIdAndOperatorIdAndStockLevelOrderByIdDesc(String storeId, Date time, String skuId, String opId,  StockLevel level,Pageable pageable);

    Page<StockChangeTransaction> findAllByStoreIdAndAddTimeGreaterThanEqualAndTypeAndStockLevelOrderByAddTimeDesc(String storeId, Date time, StockType type, StockLevel level, Pageable pageable);

    Page<StockChangeTransaction> findAllByStoreIdAndAddTimeGreaterThanEqualAndSourceAndStockLevelOrderByAddTimeDesc(String storeId, Date time, StockChangeReason source, StockLevel level, Pageable pageable);

    Page<StockChangeTransaction> findAllByStoreIdAndAddTimeGreaterThanEqualAndSourceAndTypeAndStockLevelOrderByAddTimeDesc(String storeId, Date time, StockChangeReason source, StockType type, StockLevel level, Pageable pageable);

    Page<StockChangeTransaction> findAllByStoreIdAndAddTimeGreaterThanEqualAndSourceAndSkuIdAndStockLevelOrderByAddTimeDesc(String storeId, Date time, StockChangeReason source, String skuId, StockLevel level, Pageable pageable);

    Page<StockChangeTransaction> findAllByStoreIdAndAddTimeGreaterThanEqualAndTypeAndSkuIdAndStockLevelOrderByAddTimeDesc(String storeId, Date time, StockType type, String skuId, StockLevel level, Pageable pageable);

    Page<StockChangeTransaction> findAllByStoreIdAndAddTimeGreaterThanEqualAndTypeAndSkuIdAndSourceAndStockLevelOrderByAddTimeDesc(String storeId, Date time, StockType type, String skuId, StockChangeReason source, StockLevel level, Pageable pageable);

    Page<StockChangeTransaction> findAllByStoreIdAndAddTimeGreaterThanEqualAndStockLevelAndSourceOrderByIdDesc(String storeId, Date time,  StockLevel level,StockChangeReason source,Pageable pageable);


    Page<StockChangeTransaction> findAllByStoreIdAndAddTimeGreaterThanEqualAndStockLevelAndTypeOrderByIdDesc(String storeId, Date time,  StockLevel level,StockType type,Pageable pageable);
    @Query(value = "SELECT `sku_id` FROM stock_change_transaction \n" +
            "WHERE `stock_level` ='WAREHOUSE' and add_time >= '2025-04-15 00:00:00'  and add_time <= '2025-04-16 00:00:00' GROUP BY `sku_id` ORDER BY add_time desc" ,nativeQuery = true)
    List<String> findAllByAddTimeBetweenAndStockLevelGroupBySkuIdOrderByAddTimeDesc();


    @Query(value = "SELECT SUM(`quantity`),\n" +
            "       `sku_name`,\n" +
            "       `source`,\n" +
            "       `sku_id`\n" +
            "  FROM `stock_change_transaction`\n" +
            " WHERE `store_id`=:storeId" +
            "   AND `add_time` BETWEEN :beginTime" +
            "   AND :endTime" +
            "  and `stock_level` =:stockLevel and `mold` ='PRODUCT' and `sku_id` =:productId \n" +
            "        group by `source`  ORDER BY `sku_name`" ,nativeQuery = true)
    List<Object[]> findByStoreIdGroupByStatus(@Param("storeId") String storeId,@Param("beginTime") Date begin,@Param("endTime") Date end,@Param("stockLevel") String stockLevel,@Param("productId")String productId);


    StockChangeTransaction findTop1ByStoreIdAndSkuIdAndStockLevelOrderByIdDesc(String storeId, String skuId, StockLevel stockLevel);

    StockChangeTransaction findByRefIdAndSkuIdAndStockLevel(String refId, String skuId, StockLevel stockLevel);

    StockChangeTransaction findByRefIdAndStockLevel(String refId, StockLevel stockLevel);

    StockChangeTransaction findTop1BySkuIdOrderByAddTimeDesc(String skuId);

    StockChangeTransaction findTop1BySkuIdAndAddTimeBetweenOrderByAddTimeAsc(String skuId,Date begin,Date end);

    StockChangeTransaction findTop1BySkuIdAndAddTimeBetweenOrderByAddTimeDesc(String skuId,Date begin,Date end);

    StockChangeTransaction findTop1BySkuIdAndAddTimeLessThanOrderByAddTimeAsc(String skuId,Date begin);
}
